# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
呼出任务中没有呼叫的电话，包含： 活跃任务、已完成任务
"""
import pymysql
from util import (
    logging,
    time,
    write_excel,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
    per_excel_data_num,
)


class ExportNotDialData:
    def __init__(self, ui_data):
        self.task_ids = ui_data.get("target_datas")
        self.task_type = ui_data.get("task_type")
        self.data_date = ui_data.get("data_date")
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()

    def query_count(self, task_id):
        """先查询数据量"""
        if self.task_type == "活跃任务":
            sql_count = (
                f"select count(*) from {self.db_name}.b_{self.table_index}_callout_session_shd_1 "
                f"where task_id={task_id};"
            )
        else:  # 已完成任务
            sql_count = (
                f"select count(*) from "
                f"{self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} "
                f"where task_id={task_id};"
            )
        self.cursor.execute(sql_count)
        count = self.cursor.fetchone()[0]
        logging.info(
            f"查询到{self.business}任务ID为{task_id}的{self.task_type}的{self.data_type}共计{count}条。"
        )
        return count

    def export(self):
        excel_names = list()
        for task_id in self.task_ids:
            # 先查询出呼出任务对应的场景存在哪些自定义变量
            query_scene_id_sql = f"""select scene_id from {self.db_name}.b_{self.table_index}_callout_task where
            task_id={task_id};"""
            self.cursor.execute(query_scene_id_sql)
            scene_result = self.cursor.fetchall()
            if scene_result:
                scene_id = scene_result[0][0]
                # 查询场景下用户自定义变量
                variable_list = list()
                query_variable_sql = f"""select variable_name from {self.db_name}.b_{self.table_index}_dialog_variable
                where scene_id={scene_id} and variable_type='custom';"""
                self.cursor.execute(query_variable_sql)
                variable_result = self.cursor.fetchall()
                for variable_info in variable_result:
                    variable_list.append(variable_info[0])
                sheet_title = ["任务ID", "任务名", "手机号", "手机归属地", "caseID"]
                sheet_title.extend(variable_list)
                # 需要查询的自定义变量值
                need_query_fields = list()
                if len(variable_list) > 0:
                    for variable in variable_list:
                        single_field = f", JSON_UNQUOTE(json_extract(a.detail_info, '$.{variable}'))"
                        need_query_fields.append(single_field)
                    query_fields = "".join(need_query_fields)
                else:
                    query_fields = ""
                # 查询
                if self.task_type == "活跃任务":
                    total_count = self.query_count(task_id)
                    total_batches = (
                        total_count + per_excel_data_num - 1
                    ) // per_excel_data_num
                    for i in range(total_batches):
                        sql = f"""SELECT a.task_id, b.task_name, a.dial_number, a.dial_num_district, a.customer_case_id
                        {query_fields} FROM {self.db_name}.b_{self.table_index}_callout_session_shd_1 a left join
                        {self.db_name}.b_{self.table_index}_callout_task as b on a.task_id=b.task_id WHERE
                        a.task_id='{task_id}' LIMIT {i * per_excel_data_num}, {per_excel_data_num};"""
                        logging.info(
                            f"开始查询{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]"
                            f"批次{self.task_type}的{self.data_type}SQL：\n{sql}"
                        )
                        query_start_time = time.time()
                        self.cursor.execute(sql)
                        result = self.cursor.fetchall()
                        query_end_time = time.time()
                        query_consumer_time = query_end_time - query_start_time
                        logging.info(
                            f"执行{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]"
                            f"批次{self.task_type}的{self.data_type}查询SQL共计耗时{query_consumer_time:.2f}秒。"
                        )
                        if total_batches > 1:
                            file_name = f"{self.business}_{self.task_type}的{self.data_type}_任务ID({task_id})_第{i + 1}批"
                        else:
                            file_name = f"{self.business}_{self.task_type}的{self.data_type}_任务ID({task_id})"
                        all_sheet_infos = [[self.task_type, sheet_title, result]]
                        write_start_time = time.time()
                        excel_name = write_excel(
                            all_sheet_infos,
                            save_dir=excel_save_dir,
                            file_name=file_name,
                        )
                        write_end_time = time.time()
                        write_task_time = write_end_time - write_start_time
                        logging.info(
                            f"将{self.business}{self.task_type}的第[{i + 1}/{total_batches}]"
                            f"批次{self.data_type}写入excel耗时{write_task_time:.2f}秒。"
                        )
                        excel_names.append(excel_name)
                else:  # 导出已完成任务的数据
                    total_count = self.query_count(task_id)
                    total_batches = (
                        total_count + per_excel_data_num - 1
                    ) // per_excel_data_num
                    for i in range(total_batches):
                        sql = f"""select a.task_id,b.task_name,dial_number,dial_num_district,
                        a.customer_case_id {query_fields} 
                        from {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} a left join
                        {self.db_name}.b_{self.table_index}_callout_task as b on a.task_id=b.task_id
                        WHERE a.task_id='{task_id}' AND a.memo='任务自动结束' 
                        LIMIT {i * per_excel_data_num}, {per_excel_data_num};"""
                        logging.info(
                            f"开始查询{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]"
                            f"批次{self.task_type}的{self.data_type}SQL：\n{sql}"
                        )
                        query_start_time = time.time()
                        self.cursor.execute(sql)
                        result = self.cursor.fetchall()
                        query_end_time = time.time()
                        query_consumer_time = query_end_time - query_start_time
                        logging.info(
                            f"执行{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]"
                            f"批次{self.task_type}的{self.data_type}查询SQL共计耗时{query_consumer_time:.2f}秒。"
                        )
                        if total_batches > 1:
                            file_name = f"{self.business}_{self.task_type}的{self.data_type}_任务ID({task_id})_第{i + 1}批"
                        else:
                            file_name = f"{self.business}_{self.task_type}的{self.data_type}_任务ID({task_id})"
                        all_sheet_infos = [[self.task_type, sheet_title, result]]
                        write_start_time = time.time()
                        excel_name = write_excel(
                            all_sheet_infos,
                            save_dir=excel_save_dir,
                            file_name=file_name,
                        )
                        write_end_time = time.time()
                        write_task_time = write_end_time - write_start_time
                        logging.info(
                            f"将{self.business}{self.task_type}的第[{i + 1}/{total_batches}]"
                            f"批次{self.data_type}写入excel耗时{write_task_time:.2f}秒。"
                        )
                        excel_names.append(excel_name)
        self.cursor.close()
        self.conn.close()
        if len(excel_names) > 0:
            return {"code": 0, "excel_names": excel_names, "msg": None}
        else:
            msg = (
                f"<font color=green>无法导出“{self.business}”的【{self.data_type}】，原因：\n"
                f"没有找到任务({','.join(self.task_ids)})的{self.data_type}!</font>"
            )
            return {"code": 1, "excel_names": None, "msg": msg}


if __name__ == "__main__":
    ...
